-- localhost:/usr/share/postgresql/8.3/contrib# su postgres -c "psql -d DBNAME < pgxml.sql"

-- Table: entityes

-- DROP TABLE entityes;

CREATE TABLE entityes
(
  id serial NOT NULL,
  data xml,
  CONSTRAINT entityes_pkey PRIMARY KEY (id)
)
WITH (OIDS=TRUE);


-- Function: _create(text)

-- DROP FUNCTION _create(text);

CREATE OR REPLACE FUNCTION _create(_module text)
  RETURNS SETOF entityes AS
$BODY$
DECLARE _r bigint;
DECLARE _ret entityes%rowtype;
DECLARE _sql text;
BEGIN
	_sql := 'INSERT INTO entityes (data) VALUES (\'<data module="' || _module || '" parent="none" inherite="none"/>\') RETURNING id;';
	EXECUTE _sql INTO _r;
	FOR _ret in SELECT * FROM entityes WHERE id = _r
		LOOP
		RETURN NEXT _ret;
		END LOOP;
	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


-- Function: _delete(text)

-- DROP FUNCTION _delete(text);

CREATE OR REPLACE FUNCTION _delete(_xpath text)
  RETURNS SETOF entityes AS
$BODY$
DECLARE _ret entityes%rowtype;
DECLARE _sql text;
BEGIN
	_sql := 'DELETE FROM entityes WHERE cast( xpath(\'' || _xpath || '\',data) as text) <> \'{}\' RETURNING *;';
	-- EXECUTE _sql INTO _r;
	FOR _ret in EXECUTE _sql
		LOOP
		RETURN NEXT _ret;
		END LOOP;
	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;



-- Function: _delete(xml)

-- DROP FUNCTION _delete(xml);

CREATE OR REPLACE FUNCTION _delete(_xpath xml)
  RETURNS SETOF entityes AS
$BODY$
DECLARE _ret entityes%rowtype;
DECLARE _sql text;
BEGIN
	_sql := 'DELETE FROM entityes WHERE cast( xpath(\'' || cast(_xpath as text) || '\',data) as text) <> \'{}\' RETURNING *;';
	-- EXECUTE _sql INTO _r;
	FOR _ret in EXECUTE _sql
		LOOP
		RETURN NEXT _ret;
		END LOOP;
	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


-- Function: _merge(integer, xml)

-- DROP FUNCTION _merge(integer, xml);

CREATE OR REPLACE FUNCTION _merge(_id integer, _data xml)
  RETURNS boolean AS
$BODY$
DECLARE i int;
DECLARE _new_id int;
DECLARE _sql text;
-- http://developer.postgresql.org/pgdocs/postgres/plpgsql-control-structures.html внизу
-- http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
BEGIN
	i := 0;
LOOP
        -- first try to update the key
	UPDATE entityes SET data = _data WHERE id = _id;
        IF found THEN
            RETURN true;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
	-- определённо надо добавить, потому что если б удаляли, то значения уже нет
	    IF 0 = _id THEN
		_sql := 'INSERT INTO entityes (id,data) VALUES (default,\'' || _data || '\') RETURNING id';
		EXECUTE _sql INTO _new_id;
		-- _sql := 'UPDATE '

	    ELSE
	    END IF;

            RETURN true;

        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
END LOOP;
RETURN false;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;


-- Function: _read(text)

-- DROP FUNCTION _read(text);

CREATE OR REPLACE FUNCTION _read(_xpath text)
  RETURNS SETOF entityes AS
$BODY$
DECLARE _r bigint;
DECLARE _ret entityes%rowtype;
DECLARE _sql text;
BEGIN
	_sql := 'SELECT * FROM entityes WHERE cast( xpath(\'' || _xpath || '\',data) as text) <> \'{}\' ;';
	-- EXECUTE _sql INTO _r;
	FOR _ret in EXECUTE _sql
		LOOP
		RETURN NEXT _ret;
		END LOOP;
	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


-- Function: _read(xml)

-- DROP FUNCTION _read(xml);

CREATE OR REPLACE FUNCTION _read(_xpath xml)
  RETURNS SETOF entityes AS
$BODY$
DECLARE _r bigint;
DECLARE _ret entityes%rowtype;
DECLARE _sql text;
BEGIN
	_sql := 'SELECT * FROM entityes WHERE cast( xpath(\'' || cast( _xpath as text) || '\',data) as text) <> \'{}\' ;';
	-- EXECUTE _sql INTO _r;
	FOR _ret in EXECUTE _sql
		LOOP
		RETURN NEXT _ret;
		END LOOP;
	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;


-- Function: _update(text)

-- DROP FUNCTION _update(text);

CREATE OR REPLACE FUNCTION _update(_arr text)
  RETURNS boolean AS
$BODY$
DECLARE _arr_text text[];
DECLARE _id int;
DECLARE _xml xml;
DECLARE _tmp xml[];

BEGIN
	-- START TRANSACTION;	-- объяснение работы с транзакциями http://www.sql.ru/forum/actualthread.aspx?tid=320346
	-- да и вообще, BEGIN!

	_arr_text := string_to_array(_arr, '<<>>') ;	--	это хак для текстовых строк: разделитель хмлей

	FOR i IN 1..array_upper(_arr_text,1)
	LOOP

		_xml := _arr_text[i];
		_tmp := xpath('//@id',_xml);
		_id := _tmp[1];

		IF NOT _merge(_id,_xml) THEN
			-- ROLLBACK;	-- оно и так эксепшн выстрелит
			RETURN false;
		-- надо что-то сделать
			-- EXCEPTION WHEN unique_violation THEN END;
			-- do nothing, and loop to try the UPDATE again
		END IF;

	END LOOP;

	-- COMMIT;

	RETURN true;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;


-- Function: _update(xml[])

-- DROP FUNCTION _update(xml[]);

CREATE OR REPLACE FUNCTION _update(_arr xml[])
  RETURNS boolean AS
$BODY$

DECLARE _id int;
DECLARE _xml xml;
DECLARE _tmp xml[];

BEGIN
	--_arr_text := string_to_array(_arr, '>><<') ;

	START TRANSACTION;

	FOR i IN 1..array_upper(_arr,1)
	LOOP

		_xml := _arr[i];
		_tmp := xpath('//module/@id',_xml);
		_id := _tmp[1];

		IF NOT _merge(_id,_xml) THEN
			ROLLBACK;
			RETURN false;
		-- надо что-то сделать
			--EXCEPTION WHEN unique_violation THEN END;
		END IF;

	END LOOP;

	COMMIT;

	RETURN true;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;


-- Function: _update(text[])

-- DROP FUNCTION _update(text[]);

CREATE OR REPLACE FUNCTION _update(_arr text[])
  RETURNS boolean AS
$BODY$
--DECLARE _ret entityes%rowtype;
DECLARE _sql text;
DECLARE i int;
DECLARE _id int;
DECLARE _xml xml;
DECLARE _tmp xml[];

BEGIN
	--SET _arr = ARRAY[1,2,3,4,5];
--	_sql := 'SELECT cast(\'' || cast(_arr as text) || '\' as text);';
	--EXECUTE _sql INTO _ret;
	FOR i IN 1..array_upper(_arr,1)
		LOOP

			_xml := cast ( _arr[i] as xml );
			_tmp := xpath('//@id',_xml);
			_id := _tmp[1];

			IF NOT _merge(_id,_xml) THEN
				-- ROLLBACK;	-- оно и так эксепшн выстрелит
				RETURN false;
			-- надо что-то сделать
				-- EXCEPTION WHEN unique_violation THEN END;
				-- do nothing, and loop to try the UPDATE again
			END IF;



		END LOOP;
	return true;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
